package com.ars.common.dao.activity;

import com.ars.common.beans.Active.Mysubmit;
import com.ars.common.utils.DBUtil;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class CollegeActiveMaterialMysql {
    public static ArrayList<Mysubmit> findMysubmitList(String UserID, int id) throws SQLException {
        ArrayList<Mysubmit> list = new ArrayList<>();

        String sql = "-- 左连接查询\n" +
                "SELECT m.material,m.userID,m.submitTime,u.name,m.id\n" +
                "FROM material m\n" +
                "LEFT JOIN user u ON m.userID = u.userID\n" +
                "where m.id = ? and u.userID like ? " ;


        PreparedStatement ps = DBUtil.getConnection().prepareStatement(sql);
        ps.setString(1, String.valueOf(id));
        //只取前几位
        UserID = UserID.substring(4, 5);
        ps.setString(2, "____" + UserID + "%");

        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            Mysubmit msb = new Mysubmit();
            msb.setId(rs.getInt("id"));
            msb.setMaterial(rs.getString("material"));
            msb.setUserID(rs.getString("userID"));
            msb.setSubmitTime(rs.getString("submitTime"));
            msb.setName(rs.getString("name"));
            list.add(msb);
        }

        return list;
    }
}
